﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using GenericLibrariesToolKit.BussinessException;
using GenericLibrariesToolKit.Enumeration;

namespace GenericLibrariesToolKit
{
    /// <summary>
    /// 通用数据库操作DBUtil类
    /// </summary>
    public class DBUtil : GenerateSqlUtil
    {
        /** 定义一个数据源 **/
        public static string dataSource;
        /** 定义一个数据库连接 **/
        protected SqlConnection conn;

        //实现单例模式的代码
        #region
        /// <summary>
        /// 私有构造函数
        /// </summary>
        private DBUtil() { }

        /// <summary>
        /// 初始化DBUtil类
        /// </summary>
        /// <returns>DBUnit实例</returns>
        public static DBUtil CreateDBUtil()
        {
            //测试初始化是否成功
            SqlConnection testConn = null;

            try
            {
                testConn = new SqlConnection(dataSource);
                testConn.Open();
                Console.WriteLine("\n......DBUtil初始化成功！......\n");
            }
            catch (Exception ex)
            {
                Console.WriteLine("\n......DBUtil初始化失败！......\n" + ex.Message);
            }
            finally 
            {
                if (testConn != null)
                {
                    testConn.Close();
                }
            }
          
            return DBUtilHolder.instance;
        }

        /// <summary>
        /// 获取DBUtil的一个实例
        /// </summary>
        /// <returns>DBUtil实例</returns>
        public static DBUtil GetInstance()
        {
            return DBUtilHolder.instance;
        }
        
        /// <summary>
        /// 类级内部类
        /// </summary>
        private static class DBUtilHolder
        {
            public static DBUtil instance = new DBUtil();
        }
        #endregion

        //DBUtil对于实体对象的操作
        #region

        /// <summary>
        /// 实体持久化接口
        /// </summary>
        /// <param name="entity">实体</param>
        /// <returns>成功：true，失败：false</returns>
        public bool Persist<T>(T entity)
        {
            //声明一个List容器，存放实体中有属性值的属性名称
            List<string> fieldList = new List<string>();
            //声明一个Dictionary容器，存放需要生产SqlParameter属性的属性(key)和属性值(value)
            Dictionary<string, object> dictionary = new Dictionary<string, object>();
            Entity packagedEntity = EntityManager.GetInsatnce().GetEntity(entity.GetType().ToString());

            //获取实体属性集合
            PropertyInfo[] properties = entity.GetType().GetProperties();
            //遍历实体属性集合
            foreach (PropertyInfo property in properties)
            {
                if (property.Name.Equals(packagedEntity.EntityId) && packagedEntity.Generator == PKGenerationStrategy.GUID && property.GetValue(entity, null) == null)
                {
                    //添加属性名
                    fieldList.Add(property.Name);
                    //添加属性和属性值的键值对
                    dictionary.Add(property.Name, GenerateGUID.GenerateGUID36());
                }
                //如果属性值不为null
                else if (property.GetValue(entity, null) != null)
                {
                    //添加属性名
                    fieldList.Add(property.Name);
                    //添加属性和属性值的键值对
                    dictionary.Add(property.Name, property.GetValue(entity, null));
                }
            }

            //生成sql(insert)语句
            string sql = GeneratePersistSql(entity.GetType(), fieldList);
            //调用底层接口实现对数据库的操作
            ExecuteNonQuery(sql, GenerateSqlParameter(dictionary));
            return true;
        }

        /// <summary>
        /// 根据主键删除实体
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="entityIdValue">实体标识符值</param>
        /// <returns>成功：true，失败：false</returns>
        public bool Remove(Type entityType, object entityIdValue)
        {
            //生成sql(delete)语句--
            string sql = GenerateDeleteSql(entityType, entityIdValue);
            //执行sql语句
            ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 根据条件删除实体
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="whereConditions">删除条件数组</param>
        /// <returns>影响数据库记录条数</returns>
        public long Remove(Type entityType, WhereCondition[] whereConditions)
        {

            //生成sql(delete)语句
            string sql = GenerateDeleteSql(entityType, whereConditions);
            return ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 将脱管对象同步到数据库
        /// </summary>
        /// <param name="entity">实体</param>
        /// <returns>成功：true，失败：false</returns>
        public bool Merge<T>(T entity)
        {
            //存放字段的容器
            List<string> fieldList = new List<string>();
            //存放需要生成SqlParameter参数的字段容器
            Dictionary<string, object> dictionary = new Dictionary<string, object>();

            //获取实体属性集合
            PropertyInfo[] properties = entity.GetType().GetProperties();
            //遍历实体属性集合
            foreach (PropertyInfo property in properties)
            {
                //如果属性值不为null
                if (property.GetValue(entity, null) != null)
                {
                    fieldList.Add(property.Name);
                    dictionary.Add(property.Name, property.GetValue(entity, null));
                }
            }

            //生成sql(update)语句
            string sql = GenerateMergeSql(entity.GetType(), fieldList);
            //调用底层接口实现对数据库的操作
            ExecuteNonQuery(sql, GenerateSqlParameter(dictionary));

            return true;
        }

        /// <summary>
        /// 根据实体标识符获取实体
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="entityIdValue">实体标识符值</param>
        /// <returns>实体</returns>
        public T Find<T>(Type entityType, object entityIdValue)
        {
            //获取对应的实体
            Entity packagedEntity = EntityManager.GetInsatnce().GetEntity(entityType.ToString());
            //生成sql(select)语句
            string sql = GenerateQuerySql(packagedEntity, entityIdValue);
            //获取DataTable对象
            DataTable dataTable = GetDataTable(sql.ToString());
            //获取实体属性集合
            PropertyInfo[] properties = entityType.GetProperties();
            //将值赋给实体
            if (dataTable.Rows.Count != 0)
            {
                //创建一个实体
                T entity = (T)Activator.CreateInstance(entityType);
                //利用反射调用实体属性赋值方法给属性赋值
                foreach (PropertyInfo property in properties)
                {
                    object value = dataTable.Rows[0][packagedEntity.ORMDictionary[property.Name]];
                    if (value is string || value is int ||  value is long)
                    {
                        property.SetValue(entity, value, null);
                    }
                }
                return entity;
            }

            return default(T);
        }

        /// <summary>
        /// 根据条件获取实体记录List集合
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="firstResult">记录开始索引</param>
        /// <param name="maxResult">获取最大记录条数，值为-1时表示不使用分页功能</param>
        /// <param name="whereConditions">where条件数组，值为-1时表示不使用分页功能</param>
        /// <param name="orderByDictionary">order by 条件字典</param>
        /// <returns>实体记录List集合</returns>
        public QueryResult<T> List<T>(Type entityType, int firstResult, int maxResult, WhereCondition[] whereConditions, Dictionary<string,string> orderByDictionary)
        {
            QueryResult<T> queryResult = new QueryResult<T>();
            //存放实体集合的容器
            List<T> resultList = new List<T>();
            //总的记录条数
            long totalRecord = 0;
            //获取对应的实体
            Entity packagedEntity = EntityManager.GetInsatnce().GetEntity(entityType.ToString());
            //生成sql语句
            string sql = GenerateQuerySql(entityType, firstResult, maxResult, whereConditions, orderByDictionary);

            //获取总的记录条数
            string countSql = GenerateCountSql(entityType, whereConditions);
            totalRecord = Count(countSql);

            //获取DataTable
            DataTable dataTable = GetDataTable(sql);

            //获取实体属性集合
            PropertyInfo[] properties = entityType.GetProperties();
            //将值赋给实体
            foreach (DataRow row in dataTable.Rows)
            {
                //创建一个对应的实体
                T entity = (T)Activator.CreateInstance(entityType);
                //利用反射调用实体属性赋值方法给属性赋值
                foreach (PropertyInfo property in properties)
                {
                    object value = row[packagedEntity.ORMDictionary[property.Name]];
                    if (value is string || value is int ||value is long)
                    {
                        property.SetValue(entity, value, null);
                    }
                }
                resultList.Add(entity);
            }

            //给queryResult赋值
            queryResult.ResultList = resultList;
            queryResult.TotalRecord = totalRecord;

            Console.WriteLine(sql.ToString());

            return queryResult;
        }
        /// <summary>
        /// 根据条件获取实体记录DataSet
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="firstResult">记录开始索引，值为-1时表示不使用分页功能</param>
        /// <param name="maxResult">获取最大记录条数，值为-1时表示不使用分页功能</param>
        /// <param name="whereConditions">where条件数组</param>
        /// <param name="orderByDictionary">order by 条件字典</param>
        /// <returns>实体记录DataSet</returns>
        public DataSet GetDataSet(Type entityType, int firstResult, int maxResult, WhereCondition[] whereConditions, Dictionary<string, string> orderByDictionary)
        {
            //生成sql语句
            string sql = GenerateQuerySql(entityType, firstResult, maxResult, whereConditions, orderByDictionary);
            return GetDataSet(sql);
        }

        #endregion

        /// <summary>
        /// 获取一个DataTable对象
        /// </summary>
        /// <param name="sql">一个select语句</param>
        /// <returns>DataTable对象</returns>
        public DataTable GetDataTable(string sql)
        {
            return GetDataTable(sql, null);
        }

        /// <summary>
        /// 获取一个DataTable对象
        /// </summary>
        /// <param name="sql">一个select语句</param>
        /// <param name="param">参数对象数组</param>
        /// <returns>DataTable对象</returns>
        public DataTable GetDataTable(string sql, SqlParameter[] param)
        {
            using (conn = new SqlConnection(dataSource))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                return dataTable;
            }     
        }

        /// <summary>
        /// 获取DataSet对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetDataSet(string sql)
        {
            return GetDataSet(sql, null);
        }

        /// <summary>
        /// 获取DataSet对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数数组</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetDataSet(string sql, SqlParameter[] param)
        {
            using (conn = new SqlConnection(dataSource))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                return dataSet;
            }
        }

        /// <summary>
        /// 执行非Select类型的sql语句，用于替代PersistOrMerge方法
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>影响数据库记录条数</returns>
        public long ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }

        /// <summary>
        /// 执行非Select类型的sql语句，用于替代PersistOrMerge方法
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">SqlParameter参数</param>
        /// <returns>影响数据库记录条数</returns>
        public long ExecuteNonQuery(string sql, SqlParameter[] param)
        {
            long count = -1;
            //使用using可以使数据库连接conn只在{}之内有效
            using (conn = new SqlConnection(dataSource))
            {
                //打开连接
                conn.Open();
                //获取数据库操作对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                //判断参数数组是否为空
                if (param != null)
                {
                    //添加参数数组
                    cmd.Parameters.AddRange(param);
                }
                //获得影响数据库记录条数
                count = (int)cmd.ExecuteNonQuery();

                return count;
            }
        }

        /// <summary>
        /// 获取数据库对应表的记录条数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>总的记录条数</returns>
        public long Count(string sql)
        {
            long count = -1;
            //使用using可以使数据库连接conn只在{}之内有效
            using (conn = new SqlConnection(dataSource))
            {
                //打开连接
                 conn.Open();
                //获取数据库操作对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                //获得影响数据库记录条数
                count = (int)cmd.ExecuteScalar();

                return count;
            }
        }

    }
}

